Adding a SQL 2008 Server as a DataSource to JasperServer
Ok, so at work I installed JasperServer and spent quite a lot of time in their documentation and could find the basic information of how to connect to a Microsoft SQL 2008 server.
JasperSoft has a nice installer that installs everything for you: JasperServer, Tomcat, Java, MySQL, etc…
The first problem was I had a 2008 R2 64 bit VM that I thought I would use that OS and their installer just crashed with MySQL errors. So I installed MySQL myself, and then reinstalled JasperServer and pointed to my MySQL install and it appeared to work better but there were still a lot of errors in normal use that I couldn’t find documentation on. So I am now using a 2003 R2 SP2 server and everything installed just fine, first time.
I thought Microsoft was really friendly about deploying their JDBC driver for SQL 2005/2008 and that anyone could distribute it as long as they just told Microsoft first, (maybe I am wrong and maybe MS charges to distribute it). Anyway, I expected JasperServer to install the JDBC driver for me (included in their all-encompassing package) but to my dismay, the driver wasn’t there. So that is fine, I was certain there would be a quick and easy document on how add SQL 2008 as a database. Yeah, three days later, still no documentation found….
Finally, with almost no help from the JasperServer documentation, I figured out that really it was Tomcat that needed the JDBC driver, not JasperServer. Well, it turns out that Tomcat no longer uses the CLASSPATH environment variable, so I just had to copy the SQL 2008 JDBC .jar file to a directory Tomcat did look at and I was done. It was simple.
- Download the Microsoft JDBC driver: Microsoft SQL Server JDBC Driver
- Extract to a good location. I chose
c:\program filesto extract to. - Copy the sqljdbc4.jar to your installation directory which by default is:
c:\program files\jasperserver-pro-3.5.1\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib - Restart JasperServer (there is a shortcut in the start menu to restart it).
- Log into JasperServer’s web site. http://YourServer:8080/jasperserver-pro
- Go to View | Repository.
- Click the second icon called Add Resource and select Data Source.
- Choose JDBC Data Source and click next.
- Provide any Name, Label, and Description.
- For the driver put this:
com.microsoft.sqlserver.jdbc.SQLServerDriver - For the URL put this:
jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase; - Enter the username and password.
- Select a time zone.
- Click Test and it should succeed.
Troubleshooting: If it doesn’t succeed, save anyway and restart the JasperServer and watch for any exceptions.
Wow that was easy once I figured it out. Too bad that the lack of good documentation resulted in me spending three days to figure out that I just needed to spend three seconds to copy a file.
JasperServer score card
Install = 8 (Minus two points for not working Server 2008 R2 64 bit)
Documentation = 0 (no points as so far I have not found a document that has been helpful yet.
Also I note that the JasperForge (the opensource part of JasperSoft) has a wiki, but it is nothing but headers and the data is pretty much blank.
I am not trying to knock JasperSoft with this post, but I am just trying to make sure that someone who tries to do the same thing ends up finding my post and getting their SQL 2008 server added as a data source rather quickly, instead of wading through the lack of documentation for three days.
Even to show my good will towards open source projects, I documented my steps on their wiki for them.
http://jasperforge.org/plugins/mwiki/index.php/Jasperserver/DataSources#Adding_a_Microsoft_SQL_Server_2008_Database_as_a_Data_Source
2015 and still the only documentation I could find that made any sense for a Windows based installation. Thanks for taking the time to document the process and for ending the massive frustration of trying to find the answer.
Your mode of telling everything in this paragraph is really pleasant, all be
able to easily understand it, Thanks a lot.
I'm gone to say to my little brother, that he should also pay a quick visit this website on regular basis to take updated from most up-to-date news update.
Hello,
For those who want to set a connection to SQL Server 2008 in iReport Designer, I did this :
1. Download the jdbc driver as described in the article
2. Open iReport Designer
3. Go to Tools>Options>iReport>ClassPath and click on "Add a jar"
4. Go and find the sqljdbc4.jar downloaded before
5. Restart iReport
6. Then you will be able to set a connection to SQL Server using : com.microsoft.sqlserver.jdbc.SQLServerDriver and the URL : jdbc:sqlserver://localhost:1433;databaseName=MYDATABASE
I found that I had to use a slightly different connection string and it is: jdbc:sqlserver://localhost:1433;databaseName=;
You have to be careful about the colons and semi-colons. I used the examples from microsoft to help figure out what target I was trying to hit. See: http://msdn.microsoft.com/en-us/library/ms378428.aspx
In addition to making sure that the SQL server is running (Control Panel>Administrative tools>Services>SQL Server(MSSQLSERVER) is started... See http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx for additional SQL Server plumbing issues.
Good luck
-Steward
Looks like the blog software ate my variable. After databaseName= should be the name of your database and a ;
When I create connection in iReport Designer 4.0.2 with SQL Server 2008.
I received an error :
SQL problems: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect
jdbc:sqlserver://localhost:1433;databaseName=qlks
qlks : my database name
I used JDBC Driver : MS SQLServer (2005) (com.microsoft.sqlserver.jdbc.SQLServerDriver)
Please help me!
I think you made a small error in your URL. You used a : instead of a ; just after the port number. This fixed my problems!
jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase;
must be
jdbc:sqlserver://ServerName:1433;databaseName=MyDatabase;
I try to connect jasper 4.5 to sql server 2008 r2 but connection fail. can anyone guide me.thanks in advance. Need it urgently
I need help urgently, I try to connect my jasper 4.5 to ms. sql server 2008 r2 32 bit but failed. Please guide me.thanks
Thanks.
JasperServer becomes more and more popular. And it's not that bad as some of you complain (no, I'm not working for them).
Thankyou - very helpful.
My connection string was jdbc:sqlserver://localhost\SQLEXPRESS
Thank you for writing these steps up! I've been fighting with JasperServer for a few days now because it wasn't connecting to SQL Server 2008.
Thanks, saved me time! Adding additional notes for benefit of others because I ran into different issues.
There were a few things I did first to rule out Jasperserver bugs and jasperserver specific problems.
I first tested connectivity between my laptop and a remote SQL Server 2005 database using SQL
Server Management Server Express. By doing that, I discovered several problems: (1) port 1433
TCP/IP was not opened on the remote SQL Server. (2) In my case it was necessary to specify a named instance as part of the connection string (3) I had set up login credentials incorrectly on the remote SQL Server database. I had it set up as using Windows Authentication instead of SQL Server authentication. (4) I had to make sure that the login credential had public and read access to tables
(5) Microsoft wouldn't accept my workgroup name so I just used the server's IP address. If your server is in a domain and uses DNS, you probably won't run into this issue. This server was not in a domain.
(6) The database itself was only set up to use windows authentication. I had to change a property at the database level to enable access by both SQL Server authentication and Windows authentication. (7) And of course restart SQL Server services so that the new settings would take. After I finally got a sucessful connection using Microsoft to Microsoft products, the fun started with Jasperserver 🙂
Thats where your post really saved me time. Here is my variation to show how to reference a named instance in jasperserver if you have one. It defaulted to port 1433.
jdbc:sqlserver://myServerNameOrIpAddress\myNamedInstance;databaseName=myDatabaseName;
Thank you your post save my time
Hi, I follow your steps but my JasperSoft 4.0.1 can't connect to SQL Server, still with the error "Connection Fail" and I don't understand why?!!!, can give me some advices to fix this?, and yes by the way Jaspersoft Documentation S*CKS!!!!!!
Yes. Jaspersoft documentation is a joke. I know they make money off support, but how can I tell if I'd even want to put money and further resources into using these products if I can't even get the very basics setup.
Waste of time.
That is why we didn't choose them for our project. 😉
And why I have never posted about them again.
Agree totally. But I had to use an open source solution. No budget to buy a commercial license for a better documented product. I agree that the lack of documentation for Jasperserver is a major reason why I would recommend against companies using it. It has been an extreme source of frusturation for me to get things done. Try an approach, abandon it and do it different. Repeat until you get it to work. Over and over and over. by the way, I don't have thousands of dollars to spend on their training and neither does my company.
Thanks a lot buddy.. You saved my time... Great work.. Hats off
Jayabal.
God Bless U. i was trying hard to do something similar. U have almost saved my job. I agree JASPER documentation sucks. they have only given examples of the samples provided by them. May u get the best appraisal in ur organization 🙂
Thanks. My company, LANDesk, chose to use Jasper's iReport in part of its upcoming product and it looks great. So we thought we would use the Jasper Server (a different product than iReport) for internal reporting but the Jasper Server was not as easy to work with as iReport. Hopefully it improves.
I do agree. Jasper Server is going the Microsoft way with fancy look and feel but I found a few basic bugs in it in my first few 2 days of usage itself. The exception handling is also bad.
Btw please do let me know if applications developed in Jasper Server Pro are compatible with IBM Websphere. Because the reporting module which I am designing will finally be integrated with an existing portal which runs on IBM WAS.